﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TalentPools.Model;

namespace TalentPools.DAL
{
    public class Company_BaseMeansService
    {

        public int GetPageCount(string str1)
        {
            string sql = "select count(*) from Conpany_BaseMeans where QyName like '%'+@key+'%' or QyName_en like '%'+@key+'%' or Qyhy like '%'+@key+'%' or Qytype like '%'+@key+'%' or Qyaddress like '%'+@key+'%' or Qyjj like '%'+@key+'%'";
            SqlParameter[] paras = new SqlParameter[] { 
           new SqlParameter("@key",str1)
           };
            return Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sql, paras));
        }
        public List<Company_BaseMeans> GetCompanyByPage(int pageIndex, int pageSize, string str1)
        {
            string sql = "select * from(select ROW_NUMBER() over(order by Id)RowIndex,* from Conpany_BaseMeans where QyName like '%'+@key+'%' or QyName_en like '%'+@key+'%' or Qyhy like '%'+@key+'%' or Qytype like '%'+@key+'%' or Qyaddress like '%'+@key+'%' or Qyjj like '%'+@key+'%')a where RowIndex between @begin and @end";
            SqlParameter[] paras = new SqlParameter[] { 
           new SqlParameter("@key",str1),
           new SqlParameter("@begin",pageSize*(pageIndex-1)+1),
           new SqlParameter("@end",pageIndex*pageSize)
           };
            List<Company_BaseMeans> list = new List<Company_BaseMeans>();
            using (SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, paras))
            {
                while (read.Read())
                {
                    Company_BaseMeans company = new Company_BaseMeans();
                    company.Id = Convert.ToInt32(read["Id"]);
                    company.QyName = read["QyName"].ToString();
                    company.QyName_en = read["QyName_en"].ToString();
                    company.Qyhy = read["Qyhy"].ToString();
                    company.Qytype = read["Qytype"].ToString();
                    company.Qyjj = read["Qyjj"].ToString();
                    company.Qyaddress = read["Qyaddress"].ToString();
                    company.Qyrscount = read["Qyrscount"].ToString();

                    company.Yyzz = read["Yyzz"].ToString();
                    company.Qymoney = read["Qymoney"].ToString();
                    company.Qyfrdb = read["Qyfrdb"].ToString();
                    company.Qyjj = read["Qyjj"].ToString();
                    list.Add(company);
                }
                return list;
            }
        }
        public List<Company_BaseMeans> GetCompanyBaseMeans()
        {
            string sql = "select * from Conpany_BaseMeans";
            List<Company_BaseMeans> list = new List<Company_BaseMeans>();
            using (SqlDataReader read = DBHelper.ExecuteGetReader(CommandType.Text, sql, null))
            {
                while (read.Read())
                {
                    Company_BaseMeans company = new Company_BaseMeans();
                    company.Id = Convert.ToInt32(read["Id"]);
                    company.QyName = read["QyName"].ToString();
                    company.QyName_en = read["QyName_en"].ToString();
                    company.Qyhy = read["Qyhy"].ToString();
                    company.Qytype = read["Qytype"].ToString();
                    company.Qyjj = read["Qyjj"].ToString();
                    list.Add(company);
                }
                return list;
            }
        }
        public int CheckBaseMeansCount(string companyName)
        {
            string sql = "select count(*) from conpany_BaseMeans where LoginName=@companyName";
            SqlParameter para = new SqlParameter("@companyName", companyName);
            return (int)DBHelper.ExecuteScalar(CommandType.Text, sql, para);
        }

        public bool UpdateBaseMeans(Company_BaseMeans basemean)
        {
            string sql = "update conpany_basemeans set QyName=@QyName,QyName_en=@QyName_en,Yyzz=@Yyzz,Qytype=@Qytype,Qymoney=@Qymoney,Qyfrdb=@Qyfrdb,Qyrscount=@Qyrscount,Qycltime=@Qycltime,Qyjj=@Qyjj,Qylxmobile=@Qylxmobile,Qyemail=@Qyemail,Qycz=@Qycz,Qyaddress=@Qyaddress,Qyhttp=@Qyhttp where LoginName=@LoginName";
            SqlParameter[] paras = new SqlParameter[] { 
           new SqlParameter("@LoginName",basemean.LoginName),
           new SqlParameter("@QyName",basemean.QyName),
           new SqlParameter("@QyName_en",basemean.QyName_en),
           new SqlParameter("@Yyzz",basemean.Yyzz),
           new SqlParameter("@Qytype",basemean.Qytype),
           new SqlParameter("@Qymoney",basemean.Qymoney),
           new SqlParameter("@Qyfrdb",basemean.Qyfrdb),
           new SqlParameter("@Qyrscount",basemean.Qyrscount),
           new SqlParameter("@Qycltime",basemean.Qycltime),
           new SqlParameter("@Qyjj",basemean.Qyjj),
           new SqlParameter("@Qylxmobile",basemean.Qylxmobile),
           new SqlParameter("@Qyemail",basemean.Qyemail),
           new SqlParameter("@Qycz",basemean.Qycz),
           new SqlParameter("@Qyaddress",basemean.Qyaddress),
           new SqlParameter("@Qyhttp",basemean.Qyhttp),
           };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) == 1;
        }

        public bool InsertBaseMeans(Company_BaseMeans basemean)
        {
            string sql = "insert into conpany_BaseMeans(LoginName,QyName,QyName_en,Yyzz,Qytype,Qymoney,Qyfrdb,Qyrscount,Qycltime,Qyjj,Qylxmobile,Qyemail,Qycz,Qyaddress,Qyhttp)values(@LoginName,@QyName,@QyName_en,@Yyzz,@Qytype,@Qymoney,@Qyfrdb,@Qyrscount,@Qycltime,@Qyjj,@Qylxmobile,@Qyemail,@Qycz,@Qyaddress,@Qyhttp)";
            SqlParameter[] paras = new SqlParameter[] { 
           new SqlParameter("@LoginName",basemean.LoginName),
           new SqlParameter("@QyName",basemean.QyName),
           new SqlParameter("@QyName_en",basemean.QyName_en),
           new SqlParameter("@Yyzz",basemean.Yyzz),
           new SqlParameter("@Qytype",basemean.Qytype),
           new SqlParameter("@Qymoney",basemean.Qymoney),
           new SqlParameter("@Qyfrdb",basemean.Qyfrdb),
           new SqlParameter("@Qyrscount",basemean.Qyrscount),
           new SqlParameter("@Qycltime",basemean.Qycltime),
           new SqlParameter("@Qyjj",basemean.Qyjj),
           new SqlParameter("@Qylxmobile",basemean.Qylxmobile),
           new SqlParameter("@Qyemail",basemean.Qyemail),
           new SqlParameter("@Qycz",basemean.Qycz),
           new SqlParameter("@Qyaddress",basemean.Qyaddress),
           new SqlParameter("@Qyhttp",basemean.Qyhttp),
           };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras) == 1;

        }
        /// <summary>
        /// 获取基本信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public Company_BaseMeans GetCompanyBaseMeanObj(string name)
        {
            string sql = "select * from conpany_basemeans where LoginName=@name";
            SqlParameter para = new SqlParameter("@name", name);
            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, sql, para))
            {
                if (dr.Read())
                {
                    Company_BaseMeans mean = new Company_BaseMeans();
                    mean.QyName = dr["QyName"].ToString();
                    mean.QyName_en = dr["QyName_en"].ToString();
                    mean.Yyzz = dr["Yyzz"].ToString();
                    mean.Qyhy = dr["Qyhy"].ToString();
                    mean.Qytype = dr["Qytype"].ToString();
                    mean.Qymoney = dr["Qymoney"].ToString();
                    mean.Qyfrdb = dr["Qyfrdb"].ToString();
                    mean.Qyrscount = dr["Qyrscount"].ToString();
                    mean.Qycltime = dr["Qycltime"].ToString();
                    mean.Qyjj = dr["Qyjj"].ToString();
                    mean.Qyaddress = dr["Qyaddress"].ToString();
                    mean.Qyemail = dr["Qyemail"].ToString();
                    mean.Qylxmobile = dr["Qylxmobile"].ToString();
                    mean.Qycz = dr["Qycz"].ToString();
                    mean.Qyhttp = dr["Qyhttp"].ToString();
                    return mean;
                }
                return null;
            }
        }
        /// <summary>
        /// 获取企业对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Company_BaseMeans GetCompanyById(int id)
        {
            string sql = "select * from Conpany_BaseMeas where Id=@Id";
            SqlParameter pa = new SqlParameter("@Id", id);
            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, sql, pa))
            {
                if (dr.Read())
                {
                    Company_BaseMeans mean = new Company_BaseMeans();
                    mean.QyName = dr["QyName"].ToString();
                    mean.QyName_en = dr["QyName_en"].ToString();
                    mean.Yyzz = dr["Yyzz"].ToString();
                    mean.Qyhy = dr["Qyhy"].ToString();
                    mean.Qytype = dr["Qytype"].ToString();
                    mean.Qymoney = dr["Qymoney"].ToString();
                    mean.Qyfrdb = dr["Qyfrdb"].ToString();
                    mean.Qyrscount = dr["Qyrscount"].ToString();
                    mean.Qycltime = dr["Qycltime"].ToString();
                    mean.Qyjj = dr["Qyjj"].ToString();
                    return mean;
                }
                return null;
            }
        }
    }
}
